package com.service;

import static com.def.GenericDef.E_XPINQBOX_INVALID_INPUT_BOX_ID;
import static com.def.GenericDef.RETURN_CODE_OK;
import static com.def.GenericDef.RETURN_CODE_SET_ERROR;
import static com.def.GenericDef.RETURN_CODE_UNKNOWN;
import static com.def.GenericDef.RETURN_MESG_OK;
import static com.def.GenericDef.TRX_OUT;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Service;
import org.springframework.transaction.interceptor.TransactionAspectSupport;

import com.dao.IBisMtrlDao;
import com.dao.IRetBoxDao;
import com.dao.IRetPrdInDao;
import com.dao.IRetPrdInfoDao;
import com.dao.IRetWODao;
import com.icim.service.ICIMBaseService;
import com.model.Ret_prd_in;
import com.model.Ret_prd_inId;
import com.model.Ret_wo;
import com.tx.Xpinqcus.XpinqcusI;
import com.tx.Xpinqcus.XpinqcusO;
import com.tx.Xpinqcus.XpinqcusOA;
import com.tx.Xpinqcus.XpinqcusOB;
import com.util.MqOpe;
import com.util.Util;

@Scope("prototype")
@Service("XpinqcusService")
public class XpinqcusService extends ICIMBaseService{
	private static Logger logger = Logger.getLogger(XpinqcusService.class);
	private static String TX_NAME = "XPINQCUS";
	
	@Autowired
	private XpinqcusI inTrx;
	@Autowired
	private XpinqcusO outTrx;
	
	@Autowired
	private IRetBoxDao retBoxDao;
	
	@Autowired
	private IBisMtrlDao bisMtrlDao;
	
	@Autowired
	private IRetWODao retWODao;

	@Autowired
	private IRetPrdInfoDao retPrdInfoDao;
	
	@Autowired
	private IRetPrdInDao retPrdInDao;
	
	private String gszMsgBuf = "";

	private MqOpe mqOpe;

	public String subMainProc(String strInMsg) {
		
		MqOpe mqOpe = new MqOpe();
		String reply ="";
		gszMsgBuf = "";
		inTrx = new XpinqcusI();
		outTrx = new XpinqcusO();
		outTrx.setTrx_id(TX_NAME);
		outTrx.setRtn_code(String.valueOf(RETURN_CODE_SET_ERROR));
		outTrx.setType_id(TRX_OUT);

	 	try{
			inTrx = (XpinqcusI) mqOpe.inStr2Obj(strInMsg,inTrx);
			String actionFlg = inTrx.getAction_flg();
			if(actionFlg.equals("L")){
				listWoBoxInfo();
			}
			if( RETURN_CODE_OK.equals(outTrx.getRtn_code()) ){
			}else{
				TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
			}
		}catch(Exception ex ){
			logger.error(Util.stackTraceToString(ex));
			outTrx.setRtn_code(RETURN_CODE_UNKNOWN);
			outTrx.setRtn_mesg(ex.toString());
		}finally{
			reply = mqOpe.obj2Str(outTrx);
			logger.info(reply);
		}
		return reply;
	}

	private void listWoBoxInfo() {
		List<XpinqcusOA> oaryList = new ArrayList<XpinqcusOA>();
		List<XpinqcusOB> oaryBList = new ArrayList<XpinqcusOB>();
		String cus_id="";
		Timestamp from_date;
		Timestamp to_date;
		cus_id = inTrx.getCus_id();
		from_date = inTrx.getFrom_date();
		to_date = inTrx.getTo_date();
		String hql = "FROM Ret_wo where WO_TYP='W' AND crt_timestamp>='"+
					from_date+"'AND crt_timestamp<='"+to_date+"'";
		if(!Util.isSpaceCheck(cus_id)){
			hql = hql + " AND cus_id_fk='" + cus_id +"'";
		}
		List<Ret_wo>retWoList = retWODao.find(hql);
		for(Ret_wo ret_wo : retWoList){
			XpinqcusOA oary = new XpinqcusOA();
			oary.setSo_id(ret_wo.getSo_id());
			oary.setPln_stb_date(ret_wo.getPln_stb_date());//计划来料时间
			oary.setPln_prd_qty(ret_wo.getRsv_pln_prd_qty());//生产订单计划总数量
			oary.setWo_id(ret_wo.getWo_id());
			oary.setRl_mtrl_qty(ret_wo.getPln_prd_qty());
			oary.setRl_mtrl_date(ret_wo.getBnd_box_timestamp());
			oary.setRl_prd_qty(ret_wo.getRl_prd_qty());//已投产数量
			oary.setNo_rl_prd_qty(ret_wo.getPln_prd_qty()-ret_wo.getRl_prd_qty());//未投产数量
			oary.setWh_in_prd_qty(ret_wo.getWh_in_prd_qty().intValue()+ ret_wo.getWip_wh_in_prd_qty().intValue());//成品数量
			oary.setWip_qty(ret_wo.getRl_prd_qty().intValue()-ret_wo.getWh_in_prd_qty().intValue()-ret_wo.getWip_wh_in_prd_qty().intValue()-ret_wo.getWh_out_prd_qty());//半成品数量(下线-成品)
			oary.setWh_out_prd_qty(ret_wo.getWh_out_prd_qty());//已出货数量
			oary.setNo_out_prd_qty(ret_wo.getPln_prd_qty()-ret_wo.getWh_out_prd_qty());//未出货数量(已下线-出货)
			if(!Util.isSpaceCheck(ret_wo.getSo_id())){
				Ret_wo ret_so = new Ret_wo();
				ret_so = retWODao.get(ret_wo.getSo_id());
				if(ret_so != null){
					oary.setAc_so_id(ret_so.getSo_id());
				}
			}
			/**
			 * HG/LZ/GK Count
			 */
			String sql="SELECT WO_ID_FK, "+
						"SUM(CASE WHEN PRD_GRADE='NG' THEN 1 ELSE 0 END) NG_GRADE, " +
						"SUM(CASE WHEN PRD_GRADE='LZ' THEN 1 ELSE 0 END) LZ_GRADE, " + 
						"SUM(CASE WHEN PRD_GRADE='GK' THEN 1 ELSE 0 END) GK_GRADE,"  + 
						"SUM(CASE WHEN PRD_GRADE='SC' THEN 1 ELSE 0 END) SC_GRADE "  + 
						"FROM RET_PRD_INFO " +
						"WHERE WO_ID_FK='"+ret_wo.getWo_id()+"'";
			List<Object[]> objList = retPrdInfoDao.findBySQL(sql);
			if(objList.size()!=0){
				Object woInfo[] = objList.get(0);
				
				if(woInfo[1] !=null){
					oary.setNg_grade_cnt(woInfo[1].toString());//NG数量
				}
				if(woInfo[2] != null){
					oary.setLz_grade_cnt(woInfo[2].toString());//LZ数量
				}
				if(woInfo[3] != null){
					oary.setGk_grade_cnt(woInfo[3].toString());//管控数量
				}
				if(woInfo[4] != null){
					oary.setSc_grade_cnt(woInfo[4].toString());//SC数量
				}
			}
			
			/**
			 * 实际来料
			 */
//			String sql_mtrl = "SELECT SUM(PRD_TL_QTY) MTRL_TL_QTY,RCV_TIMESTAMP "
//							+"FROM RET_PRD_IN where WO_ID_FK='"+ret_wo.getWo_id()+"' ORDER BY RCV_TIMESTAMP";
//			List<Object[]> objList_Mtrl = retPrdInDao.findBySQL(sql_mtrl);
//			
//			if(objList_Mtrl.size()>0){
//				Object mtrlInfo[] = objList_Mtrl.get(0);
//				if(mtrlInfo[1]!=null){
//					oary.setRl_mtrl_date((Timestamp) mtrlInfo[1]);//实际来料时间
//				}
//				if(mtrlInfo[0]!=null){
//					oary.setRl_mtrl_qty(mtrlInfo[0].);//实际来料数量
//				}
//			}
			oaryList.add(oary);
			/**
			 * 根据内部订单获取箱子信息(WAIT以及已经转入的INPR的箱子)
			 */
			String sql_box = "SELECT DISTINCT BOX_ID_FK,LOT_ID,NX_OPE_ID_FK,MTRL_BOX_ID_FK FROM RET_PRD_INFO " +
								"WHERE WO_ID_FK='"+ret_wo.getWo_id()+"'";
							//	+ "' AND BOX_ID_FK<>''";
			List<Object[]> objLisBox = retPrdInfoDao.findBySQL(sql_box);
			for (int i = 0; i < objLisBox.size(); i++) {
				XpinqcusOB oaryB = new XpinqcusOB();
      				Object boxInfo[] = objLisBox.get(i);
				if(boxInfo[0] != null){
					oaryB.setBox_id(boxInfo[0].toString());
				}
				if(boxInfo[1] != null){
					oaryB.setLot_id(boxInfo[1].toString());
				}
				if(boxInfo[2] != null){
					oaryB.setOpe_no(boxInfo[2].toString());
				}
				if(boxInfo[3] != null){
					oaryB.setMtrl_box_id(boxInfo[3].toString());
				}
				oaryB.setWo_id(ret_wo.getWo_id());
				
				Ret_prd_in ret_prd_in = new Ret_prd_in();
				Ret_prd_inId ret_prd_inId = new Ret_prd_inId();
                ret_prd_inId.setMtrl_box_id(boxInfo[3].toString());
				ret_prd_inId.setCus_id(ret_wo.getCus_id_fk());
				ret_prd_in= retPrdInDao.get(ret_prd_inId);
				if (null == ret_prd_in) {
					outTrx.setRtn_code(RETURN_CODE_UNKNOWN);
					outTrx.setRtn_mesg("[MTRL_BOX_ID]='"+boxInfo[3].toString()+"' AND [CUS_ID]='"+ret_wo.getCus_id_fk()+"' 在表RET_PRD_IN中找不到!");
					return;
				}
				oaryB.setPrd_tl_qty(ret_prd_in.getPrd_tl_qty());
				oaryB.setPrd_proc_qty(ret_prd_in.getPrd_proc_qty());
				String sqlcnt="SELECT MTRL_BOX_ID_FK,SUM(CASE WHEN PRD_STAT='COMP' THEN 1 ELSE 0 END) PRD_COMP_QTY FROM RET_PRD_INFO " +
				"WHERE MTRL_BOX_ID_FK='"+boxInfo[3].toString()+"'";
	             List<Object[]> objListcnt = retPrdInfoDao.findBySQL(sqlcnt);
	             Object cntInfo[] = objListcnt.get(0);
	             String comcnt=cntInfo[1].toString();
		         oaryB.setPrd_comp_qty(Integer.valueOf(comcnt));//NG数量
		         oaryB.setPrd_notcomp_qty(ret_prd_in.getPrd_tl_qty()-Integer.valueOf(comcnt));
		
				oaryBList.add(oaryB);
			}
		}
//		String hql = "FROM Bis_mtrl where mtrl_mkr=:mtrl_mkr";
//		Session session = bisMtrlDao.getCrSession();
//		Query query = session.createQuery(hql);
//		query.setString("mtrl_mkr", inTrx.getCus_id());
//		List<Bis_mtrl> bisMtrlList = query.list();
//		for(Bis_mtrl bis_mtrl:bisMtrlList){
//			hql = "FROM Ret_wo where mtrl_prod_id_fk=:mtrl_prod_id_fk";
//			query =session.createQuery(hql);
//			query.setString("mtrl_prod_id_fk", bis_mtrl.getMtrl_prod_id());
//			List<Ret_wo> retWoList = query.list();
//			for(Ret_wo ret_wo:retWoList){
//				hql = "FROM Ret_box where wo_id_fk=:wo_id_fk";
//				query = session.createQuery(hql);
//				query.setString("wo_id_fk",ret_wo.getWo_id());
//				List<Ret_box> retBoxList = query.list();
//				for(Ret_box ret_box:retBoxList){
//					XpinqcusOA oary = new XpinqcusOA();
//					oary.setCus_id(inTrx.getCus_id());
//					oary.setSo_id(ret_wo.getSo_id());
//					oary.setWo_id(ret_wo.getWo_id());
//					oary.setBox_id(ret_box.getBox_id());
//					oary.setLot_id(ret_box.getLot_id_fk());
//					oary.setOpe_ver(ret_box.getNx_ope_ver_fk());
//					oary.setOpe_id(ret_box.getNx_ope_id_fk());
//					oaryList.add(oary);
//				}
//			}
//		}
		outTrx.setOary(oaryList);
		outTrx.setOaryB(oaryBList);
		outTrx.setTbl_cnt(oaryList.size());
		outTrx.setTbl_cntB(oaryBList.size());
		outTrx.setRtn_code(RETURN_CODE_OK);
		outTrx.setRtn_mesg(RETURN_MESG_OK);
	}

}
